import json
from typing import Union, Optional, List

from sqlalchemy import update, delete
from config import create_db_and_tables, session
from sqlmodel import SQLModel, Field, select, or_, Relationship


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str
    # heroes: List["Hero"] = Relationship(back_populates="team")
    # cascade_delete 是级联删除，当删除一个对象时，会级联删除关联的对象
    heroes: list["Hero"] = Relationship(back_populates="team", cascade_delete=True)


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)
    # ondelete 可以有以下值 ：
    #   CASCADE：当相关记录（团队）被删除时，自动删除此记录 （英雄）。
    #   SET NULL：当相关记录被删除时，将此 外键 (hero.team_id) 字段设置为 NULL。
    #   RESTRICT：如果存在外键值，则通过引发错误 阻止 删除此记录（英雄）。
    team_id: Optional[int] = Field(default=None, foreign_key="team.id", ondelete="CASCADE")

    team: Optional[Team] = Relationship(back_populates="heroes")


# cascade_delete 或 ondelete

# 此时，您可能想知道是否应该使用 cascade_delete 或 ondelete。 答案是：两者都用！ 🤓
#   ondelete 将 配置数据库，以防有人直接与其交互；
#   cascade_delete 来告诉 SQLAlchemy 它应该删除内存中的 Python 对象。


def select_heroes_1():
    statement = select(Hero).where(Hero.name == "Spider-Boy")
    result = session.exec(statement)
    hero_spider_boy = result.one_or_none()
    print("====>获取英雄的分组信息:", hero_spider_boy)


# select_heroes_1()

def select_teams_2():
    statement = select(Team).where(Team.name == "Z-Force")
    result = session.exec(statement)
    team_preventers = result.one_or_none()
    print("====>获取分组的英雄信息:", team_preventers.heroes)


# select_teams_2()


# 移除关系
def update_heroes():
    statement = select(Hero).where(Hero.name == "Spider-Boy")
    result = session.exec(statement)
    hero_spider_boy = result.one()

    hero_spider_boy.team = None
    session.add(hero_spider_boy)
    session.commit()

    session.refresh(hero_spider_boy)
    print("Spider-Boy without team:", hero_spider_boy)


# update_heroes()
